﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.ComponentModel;
using System.IO;

// imports for the MySql Client library
using MySql.Data;
using MySql.Data.MySqlClient;
//

namespace kiosk
{

    // Following classes in this module provides classes to connect to the database and manage it
    class DataBaseConn
    {

        private MySqlConnection conn;

        public DataBaseConn()
        {

        }

        public void connect()
        {
            if (conn != null)
                return;
            else
            {

                // if the object is not connect to the database

                try
                {
                    conn = new MySqlConnection("server=" + GeneralOpt.ServerAddress + ";user=" + GeneralOpt.DatabaseUserName + ";database=" + GeneralOpt.DatabaseName + ";port=" +GeneralOpt.ServerPort + ";password=" + GeneralOpt.DatabaseUserPass);
                    conn.Open();
                }
                catch (Exception ex)
                {

                    throw new Exception(ex.Message);
                }

            }
        }

        public bool isConnected()
        {
            return conn.Ping();

        }

        public void reConnect()
        {
            conn.Open();

        }



        // Attributes 
        public MySqlConnection MysqlConnObject
        {
            get
            {
                try
                {
                    this.connect();
                    return conn;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }

            }


        }


    } // end of the class databaseconn

    public class DataBaseHandler
    {

        private DataBaseConn myconn;

        public DataBaseHandler()
        { }

        public void parpreConn()
        {

            myconn = new DataBaseConn();
            myconn.connect();

        }
        public int isLanGroupPresent(int lan)
        {
            // check if the data exists in the specifies group and the speciated language
            try
            {
                if (myconn == null)
                    this.parpreConn();
                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                string sql = " select count(*) from groups where LanID=@languageid";

                MySqlCommand command = new MySqlCommand(sql, myconn.MysqlConnObject);
                command.Parameters.AddWithValue("@languageid", lan);
                object result = command.ExecuteScalar();

                return Convert.ToInt32(result);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }



        }


        /// <summary>
        /// Returns parent group id 
        /// 
        /// </summary>
        /// <param name="gid"> Group id </param>
        /// <returns> Parent group id </returns>

        /*
        public Int32 getGroupIDOnItemID(Int32 itemID) // should be isParentGroup() // have to fix it
        {
            try
            {
                if (myconn == null)
                    this.parpreConn();

                string sql = "select GroupID from groupitems where ItemID=@itemid";

                MySqlCommand command = new MySqlCommand(sql, myconn.MysqlConnObject);
                command.Parameters.AddWithValue("@id",itemID );
                object result = command.ExecuteScalar();
                if (result == null)
                    return -1;
                else
                    return (Int32)result; 
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }
              
        }
        
         */

        // get the language based on the group id 

        public string getLanguageNameOnGID(Int32 gid)
        {

            string sql = "select LanName from language l , groups g where l.LanID=( select LanID from groups where GroupID=@gid) limit 1";
            try
            {

                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@gid", gid);
                object result = mcomm.ExecuteScalar();
                mcomm.Dispose();
                if (result != null)
                    return Convert.ToString(result);
                else
                    return "";



            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);


            }



        }


        // get parent group id from the group id given in the parameters 

        public Int32 getParentGIDFGroup(Int32 gid)
        {
            string sql = " select ParentGID from groups where GroupID=@gid";
            Int32 pgid;

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@gid", gid);
                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull)
                    pgid = Convert.ToInt32(result);
                else
                {
                    pgid = -1;
                }

                return pgid;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "Error occured at the database");

            }


        }



        // Get the Datatype based on the dataID
        public Int32 getDataType(Int32 DataID)
        {
            string sql = "Select DataType from data where DataID=@dataid";

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("dataid", DataID);
                Int32 Dtype;
                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull)
                    Dtype = Convert.ToInt32(result);
                else
                {
                    Dtype = -1;
                }

                return Dtype;


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + "Add info : Error occured at DataBase operation");

            }



        }


        // Get all the language names from the database

        public List<string> getAllLanguageNames()
        {
            string sql = "select LanName from language";
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                MySqlDataReader myreader = mcomm.ExecuteReader();
                List<string> names = new List<string>();
                while (myreader.Read())
                {

                    names.Add(Convert.ToString(myreader[0]));

                }

                myreader.Close();
                return names;


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + "error from the database");
            }




        }




        // Returns All the group iteams in a group
        // if there is no item in a group it will return 0

        public DataTable getAllGroupItems(int gid)
        {

            MySqlDataAdapter dataadpter;
            DataTable tempdatatable;
            string sql = "select * from groupitem where GroupID=@gid order by ItemID asc";

            try
            {

                if (myconn == null)
                    this.parpreConn();
                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Parameters.AddWithValue("@gid", gid);
                mcomm.Connection = myconn.MysqlConnObject;

                dataadpter = new MySqlDataAdapter(mcomm);
                tempdatatable = new DataTable();
                dataadpter.Fill(tempdatatable);
                return tempdatatable;


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }


        }

        // The following function adds Button labels in a group. Button label here represented ad an item so 
        // the below function adds many items to a group.
        // The MySqlCommandobject here uses parpre statement to optimize database performance.
        public void addBtnSInGroup(int gid, List<string> btnLabels)
        {

            string sql = "insert into groupitem(ItemID , DataID, GroupID, ItemText , ItemGroup ) values( @itemID, NULL , @gID , @btntext, NULL)";
            string sql2 = "select MAX(ItemID) from groupitem";
            MySqlCommand mcmd;
            try
            {

                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                mcmd = new MySqlCommand();
                mcmd.Connection = myconn.MysqlConnObject;
                mcmd.CommandText = sql2;
                object result = mcmd.ExecuteScalar();
                Int32 nItemId;

                if (result == Convert.DBNull)
                    nItemId = 1;
                else
                    nItemId = Convert.ToInt32(result) + 1;

                // now inserting dataitems
                mcmd.CommandText = sql;


                mcmd.Parameters.AddWithValue("@itemID", 1);
                mcmd.Parameters.AddWithValue("@gid", 1);
                mcmd.Parameters.AddWithValue("@btntext", "BTNTEXT");
                mcmd.Prepare();
                int i = 0;
                foreach (string str in btnLabels)
                {
                    mcmd.Parameters["@itemID"].Value = nItemId;
                    mcmd.Parameters["@gID"].Value = gid;
                    mcmd.Parameters["@btntext"].Value = str;
                    mcmd.ExecuteNonQuery();
                    nItemId = nItemId + 1;
                    i++;

                }




            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

        }

        // The function that returns all items in the database


        public Int32 isItemDataPresent(Int32 itemID)
        {
            string sql = "select DataID from groupitem where ItemID=@itemid";
            try
            {

                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }



                MySqlCommand mcom = new MySqlCommand(sql, myconn.MysqlConnObject);
                mcom.Parameters.AddWithValue("@itemid", itemID);
                object result = mcom.ExecuteScalar();

                if (result == Convert.DBNull || result == null)
                    return -1;
                else
                    return Convert.ToInt32(result);


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }


        }

        // get groupid based on the item id

        public Int32 getGroupIDOnItemID(Int32 itemid)
        {
            string sql = "select GroupID from groupitem where ItemID=@itd";
            try
            {

                if (myconn == null)
                {
                    this.parpreConn();

                }

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcom = new MySqlCommand(sql, myconn.MysqlConnObject);
                mcom.Parameters.AddWithValue("@itd", itemid);
                object result = mcom.ExecuteScalar();
                return (Int32)result;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }



        }

        // the below function adds a new table with the specified headers

        public Int32 createNewTable(Int32 tabletype, List<string> tableheader)
        {

            string mdataid = "select max(DataID) from data";
            string idata = "insert into data values(@dataid, @datatype)";
            string itable = "insert into tabledata values(@dataid , 'kiosktable')";
            string mcolheader = "select max(ColHeaderID) from columnheader";
            string itheader = "insert into columnheader values (@colid , @dataid , @text )";
            Int32 maxdataid;
            if (myconn == null)
                this.parpreConn();
            MySqlTransaction mytran = null;
            try
            {

                Int32 maxcolhid;
                MySqlCommand mcom = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();
                mcom.Transaction = mytran;
                mcom.Connection = myconn.MysqlConnObject;
                mcom.CommandText = mdataid;

                object result = mcom.ExecuteScalar();
                if (result != Convert.DBNull)
                    maxdataid = (Int32)result + 1;
                else
                    maxdataid = 0;

                mcom.CommandText = idata;
                mcom.Parameters.AddWithValue("@dataid", maxdataid);
                mcom.Parameters.AddWithValue("@datatype", tabletype);
                mcom.ExecuteNonQuery();
                mcom.CommandText = itable;
                mcom.Parameters["@dataid"].Value = maxdataid;
                mcom.ExecuteNonQuery();
                // now add the headers in the table
                mcom.CommandText = mcolheader;
                result = mcom.ExecuteScalar();
                if (result != Convert.DBNull)
                    maxcolhid = (Int32)result + 1;
                else
                    maxcolhid = 0;

                mcom.CommandText = itheader;
                mcom.Parameters["@dataid"].Value = maxdataid;
                mcom.Parameters.AddWithValue("@colid", maxcolhid);
                mcom.Parameters.AddWithValue("@text", "Garbage data");

                foreach (string text in tableheader)
                {
                    mcom.Parameters["@colid"].Value = maxcolhid;
                    mcom.Parameters["@text"].Value = text; ;
                    mcom.ExecuteNonQuery();
                    maxcolhid++;

                }

                mytran.Commit();
                return maxdataid;

            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);

            }

        }
        // insert a new row in the table

        public Int32 insertANewRow(Int32 tableid, List<string> colval) // id of the table)
        {

            string mrowid = "select max(RowID) from rows";
            string mcolvalid = "select max(ColID) from columndata";
            string irow = "insert into rows values(@rowid , @tid , @name)";
            string icolval = "insert into columndata values (@colvalid , @rowid , @data)";
            MySqlTransaction mytran = null;
            if (myconn == null)
                this.parpreConn();
            Int32 maxrowid;
            try
            {

                Int32 maxcolvalid;

                MySqlCommand mcom = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();

                // began a tranjection in the database

                mcom.Transaction = mytran;
                mcom.Connection = myconn.MysqlConnObject;
                mcom.CommandText = mrowid;
                object result = mcom.ExecuteScalar();
                if (result != Convert.DBNull)
                    maxrowid = Convert.ToInt32(result) + 1;
                else
                    maxrowid = 0;
                mcom.CommandText = mcolvalid;
                result = mcom.ExecuteScalar();
                if (result != Convert.DBNull)
                    maxcolvalid = Convert.ToInt32(result) + 1;
                else
                    maxcolvalid = 0;
                // insert a row in the database
                mcom.CommandText = irow;
                mcom.Parameters.AddWithValue("@rowid", maxrowid);
                mcom.Parameters.AddWithValue("@tid", tableid);
                mcom.Parameters.AddWithValue("@name", "my sample row");
                mcom.ExecuteNonQuery();
                mcom.CommandText = icolval;
                mcom.Prepare();
                mcom.Parameters.AddWithValue("@colvalid", maxcolvalid);
                mcom.Parameters["@rowid"].Value = maxrowid;
                mcom.Parameters.AddWithValue("@data", "text");
                foreach (string str in colval)
                {
                    mcom.Parameters["@colvalid"].Value = maxcolvalid;
                    mcom.Parameters["@data"].Value = str;
                    mcom.ExecuteNonQuery();
                    maxcolvalid++;


                }

                // Now commit the database
                mytran.Commit();
                mcom.Dispose();
                return maxrowid;


            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);
            }
        }

        // get all the row data from the table

        public DataTable getAllTableData(Int32 tableid)
        {

            string sql = "select r.RowID, c.ColID, c.ColTextData from tabledata t inner Join rows r inner join columndata c on c.RowID=r.RowID and r.DataID=t.DataID and t.DataID=@dataid order by r.RowID asc";
            DataTable tabledata = null;
            if (myconn == null)
                this.parpreConn();
            try
            {
                MySqlDataAdapter daTableData;
                tabledata = new DataTable();
                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@dataid", tableid);
                daTableData = new MySqlDataAdapter(mcomm);
                daTableData.Fill(tabledata);

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            return tabledata;


        }

        // get all the column headers from the table 

        public DataTable getTableColNames(Int32 tableid)
        {

            string sql = "select c.ColHeaderID , c.Text from columnheader c , tabledata t where t.DataID = c.DataID and t.DataID=@dataid";
            DataTable tabledata;
            if (myconn == null)
                this.parpreConn();
            try
            {

                MySqlDataAdapter daTableData;
                tabledata = new DataTable();
                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@dataid", tableid);
                daTableData = new MySqlDataAdapter(mcomm);
                daTableData.Fill(tabledata);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }
            return tabledata;


        }

        // deletes the table contents 
        public Int32 delTableAndData(Int32 dataid)
        {
            string colval = "delete columndata from columndata , rows where columndata.RowID=rows.RowID and rows.RowID=(select RowID from rows where DataID=@tid)";
            string colheader = "delete from columnheader where DataID=@tid";
            string rowdel = "delete from rows where DataID=@tid";
            string dtable = "delete from tabledata where DataID=@tid";
            string ddata = "delete from data where DataID=@tid";
            MySqlTransaction mytran = null;
            if (myconn == null)
                this.parpreConn();

            try
            {
                MySqlCommand mcomm = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();

                // began a tranjection in the database

                mcomm.Transaction = mytran;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = colval;
                mcomm.Parameters.AddWithValue("@tid", dataid);
                mcomm.ExecuteNonQuery();
                //
                mcomm.CommandText = colheader;
                mcomm.Parameters["@tid"].Value = dataid;
                mcomm.ExecuteNonQuery();
                //
                mcomm.CommandText = rowdel;
                mcomm.Parameters["@tid"].Value = dataid;
                mcomm.ExecuteNonQuery();
                //
                mcomm.CommandText = dtable;
                mcomm.Parameters["@tid"].Value = dataid;
                mcomm.ExecuteNonQuery();
                //
                mcomm.CommandText = ddata;
                mcomm.Parameters["@tid"].Value = dataid;
                mcomm.ExecuteNonQuery();
                //

                mytran.Commit();
            }


            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);

            }

            return 1;


        }

        public void delRows(Int32 tableid, Int32[] index)
        {
            string colval = "delete columndata from columndata , rows where columndata.RowID=rows.RowID and rows.RowID=@rid and rows.DataID=@tid";
            string rowdel = "delete rows from rows  where RowID=@rid and DataID=@tid";
            MySqlTransaction mytran = null;
            if (myconn == null)
                this.parpreConn();
            try
            {

                mytran = myconn.MysqlConnObject.BeginTransaction();
                MySqlCommand mcomm = new MySqlCommand();
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Transaction = mytran;
                mcomm.CommandText = colval;
                mcomm.Prepare();

                //

                mcomm.Parameters.AddWithValue("@rid", 0);
                mcomm.Parameters.AddWithValue("@tid", tableid);

                foreach (Int32 rowid in index)
                {
                    mcomm.Parameters["@rid"].Value = rowid;
                    mcomm.Parameters["@tid"].Value = tableid;
                    mcomm.ExecuteNonQuery();
                    mcomm.CommandText = rowdel;
                    mcomm.Parameters["@tid"].Value = tableid;
                    mcomm.Parameters["@rid"].Value = rowid;
                    mcomm.ExecuteNonQuery();

                }

                mytran.Commit();

            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);

            }

        }


        // insert a row at the specific location
        public void insertRowAt(Int32 tableid, Int32 nrowid, List<string> colval) // id of the table)
        {

            string mcolvalid = "select max(ColID) from columndata";
            string irow = "insert into rows values(@rowid , @tid , @name)";
            string icolval = "insert into columndata values (@colvalid , @rowid , @data)";
            MySqlTransaction mytran = null;
            //Int32 maxrowid;
            if (myconn == null)
                this.parpreConn();
            try
            {

                Int32 maxcolvalid;

                MySqlCommand mcom = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();

                // began a tranjection in the database

                mcom.Transaction = mytran;
                mcom.Connection = myconn.MysqlConnObject;

                //
                mcom.CommandText = mcolvalid;
                object result = mcom.ExecuteScalar();
                if (result != Convert.DBNull)
                    maxcolvalid = Convert.ToInt32(result) + 1;
                else
                    maxcolvalid = 0;

                // insert a row in the database

                mcom.CommandText = irow;
                mcom.Parameters.AddWithValue("@rowid", nrowid);
                mcom.Parameters.AddWithValue("@tid", tableid);
                mcom.Parameters.AddWithValue("@name", "my sample row");
                mcom.ExecuteNonQuery();
                mcom.CommandText = icolval;
                mcom.Prepare();
                mcom.Parameters.AddWithValue("@colvalid", maxcolvalid);
                mcom.Parameters["@rowid"].Value = nrowid;
                mcom.Parameters.AddWithValue("@data", "text");
                foreach (string str in colval)
                {
                    mcom.Parameters["@colvalid"].Value = maxcolvalid;
                    mcom.Parameters["@data"].Value = str;
                    mcom.ExecuteNonQuery();
                    maxcolvalid++;


                }

                // Now commit the database
                mytran.Commit();
                mcom.Dispose();
                // return maxrowid;


            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public Int32 saveNewTxtData(string filename, string dataHeading, Int32 dtype, byte dataopt)
        {
            string sqlmdata = "select max(DataID) from data";
            string sqlidata = "insert into data (DataID,DataType,DisOpt,DataHeading) values(@did,@dtype,@dataopt,@datahead)";
            string sqlitdata = "insert into textdata values(@did,@text,@size)";

            long FileSize;
            byte[] rawData;
            FileStream fs = null;
            Int32 mdataid;
            try
            {

                fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
                FileSize = fs.Length;

                rawData = new byte[FileSize];

                fs.Read(rawData, 0, (int)FileSize);

                fs.Close();


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "Addinfo : Error from file accress");

            }


            MySqlTransaction mytran = null;
            if (myconn == null)
                this.parpreConn();

            if (!myconn.isConnected())
            {
                this.myconn.reConnect();

            }



            try
            {
                MySqlCommand mcomm = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();
                mcomm.Transaction = mytran;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = sqlmdata;

                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull)
                    mdataid = Convert.ToInt32(result) + 1;
                else
                    mdataid = 1;
                mcomm.CommandText = sqlidata;
                mcomm.Parameters.AddWithValue("@did", mdataid);
                mcomm.Parameters.AddWithValue("@dtype", dtype);
                mcomm.Parameters.AddWithValue("@dataopt", dataopt);
                mcomm.Parameters.AddWithValue("@datahead", dataHeading);
                mcomm.ExecuteNonQuery();
                mcomm.CommandText = sqlitdata;
                mcomm.Parameters.AddWithValue("@text", rawData);
                mcomm.Parameters.AddWithValue("@size", FileSize);
                mcomm.ExecuteNonQuery();
                mytran.Commit();
                return mdataid;
            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);
            }



        }

        public void dTextData(Int32 dataid)
        {


            string sqlDData = "delete data from data where DataID=@did";
            string sqlDTextData = "delete textdata from textdata where DataID=@did";


            MySqlTransaction mytran = null;
            if (myconn == null)
                this.parpreConn();

            if (!myconn.isConnected())
            {
                this.myconn.reConnect();

            }


            try
            {
                MySqlCommand mcomm = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();
                mcomm.Transaction = mytran;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = sqlDTextData;
                mcomm.Parameters.AddWithValue("@did", dataid);
                mcomm.ExecuteNonQuery();
                mcomm.CommandText = sqlDData;
                mcomm.ExecuteNonQuery();
                mytran.Commit();

            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);
            }

        }

        public Byte[] getTxtData(Int32 dataid)
        {
            string sql = "select Text , Size from textdata where DataID=@dataid";
            UInt32 FileSize;
            byte[] rawData;
            FileStream fs;
            MySqlDataReader myreader;
            if (myconn == null)
                this.parpreConn();

            if (!myconn.isConnected())
            {
                this.myconn.reConnect();

            }


            try
            {
                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@dataid", dataid);
                myreader = mcomm.ExecuteReader();
                if (!myreader.HasRows)
                    throw new Exception("There are no BLOBs to save");

                myreader.Read();
                FileSize = myreader.GetUInt32(myreader.GetOrdinal("Size"));
                rawData = new byte[FileSize];

                myreader.GetBytes(myreader.GetOrdinal("Text"), 0, rawData, 0, (int)FileSize);

                myreader.Close();
                fs = new FileStream(@"temp.pdf", FileMode.Create, FileAccess.Write);
                fs.Write(rawData, 0, (int)FileSize);
                fs.Close();
                return rawData;

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }


        }

        public void addDataIDOnItem(Int32 itemid, Int32 dataID)
        {
            string sql = "update groupitem set DataID=@dataid where ItemID=@itemid";
            MySqlTransaction mytran = null;
            if (myconn == null)
                this.parpreConn();

            if (!myconn.isConnected())
            {
                this.myconn.reConnect();

            }


            try
            {

                MySqlCommand mcomm = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@dataid", dataID);
                mcomm.Parameters.AddWithValue("@itemid", itemid);
                mcomm.ExecuteNonQuery();
                mytran.Commit();



            }
            catch (Exception ex)
            {

                mytran.Rollback();
                throw new Exception(ex.Message);
            }

        }


        /// <summary>
        /// creates a buttongroup and it creates more then one button in the next group
        /// all the button created in the all the languages. 
        /// </summary>
        /// <param name="gid"> groupid in which the button will be created</param>
        /// <param name="nbtn"> the number of button to be created </param>

        public List<Int32> createBtnGroup()
        {

            string sql = "select LanID from language";
            string gmsql = "select max(GroupID) from groups";
            string insertsql = "insert into groups(GroupID , LanID , ParentGID ) values (@gid , @lid , null)";
            MySqlTransaction mytran = null;
            Int32 maxgid;
            List<Int32> gList = new List<Int32>();
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();
                mytran = this.myconn.MysqlConnObject.BeginTransaction();
                mcomm.Transaction = mytran;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = gmsql;
                object result = mcomm.ExecuteScalar();
                if (result != null && result != Convert.DBNull)
                {
                    maxgid = Convert.ToInt32(result);

                }
                else
                    maxgid = 1;

                mcomm.CommandText = sql;
                MySqlDataReader mreader = mcomm.ExecuteReader();
                List<Int32> lanid = new List<int>();
                if (mreader.HasRows)
                {
                    while (mreader.Read())
                    {
                        lanid.Add(mreader.GetInt32("LanID"));

                        //lanid[lanid.Count - 1] = mreader.GetInt32("LanID");

                    }


                }
                mreader.Close();
                mcomm.CommandText = insertsql;
                mcomm.Parameters.AddWithValue("@gid", 0);
                mcomm.Parameters.AddWithValue("@lid", 0);
                if (lanid.Count > 0)
                    foreach (Int32 num in lanid)
                    {
                        maxgid += 1;
                        mcomm.Parameters["@gid"].Value = maxgid;
                        mcomm.Parameters["@lid"].Value = num;
                        gList.Add(maxgid);
                        mcomm.ExecuteNonQuery();

                    }




                mytran.Commit();
                return gList;

            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message + "Error from the database");

            }

        }

        /// <summary>
        /// updates the ParentGID of a group
        /// </summary>
        /// <param name="Cgid"> The Cgig which the pgid has to be set</param>
        /// <param name="Pgid"> the ParentGID value</param>
        public void updateParentGID(Int32 Cgid, Int32 Pgid)
        {
            string sql = "update groups set ParentGID=@pgid where GroupID=@gid";
            MySqlTransaction mytran = null;
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();
                mytran = this.myconn.MysqlConnObject.BeginTransaction();
                mcomm.Transaction = mytran;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = sql;
                mcomm.Parameters.AddWithValue("@pgid", Pgid);
                mcomm.Parameters.AddWithValue("@gid", Cgid);
                mcomm.ExecuteNonQuery();
                mytran.Commit();


            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message + "Error ffrom the database");

            }


        }









        // Creates the button text and also creates the buttton relation i.e item relationship
        // most dificult function in the module

        public void createitemInGroup(List<Int32> gids, List<List<string>> btnText, List<List<string>> btnFont)
        {


            // Now update the each itemgroup fields

            string sql = "insert into groupitem(ItemID , DataID, GroupID, ItemText , ItemGroup, ItemFont) values( @itemID, NULL , @gID , @btntext, @itemgr, @Itemfont)";
            string sql2 = "select MAX(ItemID) from groupitem";
            if (btnText.Count != btnFont.Count)
            {
                throw new ArgumentException("The no of buttons and their font data dont match  !!!");
            }


            MySqlCommand mcmd;
            MySqlTransaction mytran = null;

            try
            {

                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                mcmd = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();
                mcmd.Connection = myconn.MysqlConnObject;
                mcmd.CommandText = sql2;
                mcmd.Transaction = mytran;
                object result = mcmd.ExecuteScalar();
                Int32 nItemId;

                if (result == Convert.DBNull)
                    nItemId = 1;
                else
                    nItemId = Convert.ToInt32(result);

                // now inserting dataitems
                mcmd.CommandText = sql;


                mcmd.Parameters.AddWithValue("@itemID", 1);
                mcmd.Parameters.AddWithValue("@gid", 1);
                mcmd.Parameters.AddWithValue("@btntext", "BTNTEXT");
                mcmd.Parameters.AddWithValue("@itemgr", Convert.DBNull);
                mcmd.Parameters.AddWithValue("@Itemfont", "null");
                mcmd.Prepare();
                int k = 0;
                int row = 0;
                int col = 0;
                int rownum = btnText.Count;
                int colnum = btnText[0].Count;


                for (; col < colnum; col++)
                {
                    for (row = 0; row < rownum; row++)
                    {
                        if (row == 0)
                        {
                            nItemId++;
                            mcmd.Parameters["@itemgr"].Value = Convert.DBNull;
                            mcmd.Parameters["@itemid"].Value = nItemId;
                            mcmd.Parameters["@gid"].Value = gids[k];
                            mcmd.Parameters["@btntext"].Value = btnText[row][col];
                            mcmd.Parameters["@Itemfont"].Value = btnFont[row][col];

                            mcmd.ExecuteNonQuery();

                        }
                        else
                        {
                            mcmd.Parameters["@itemgr"].Value = nItemId;
                            nItemId++;
                            k++;
                            mcmd.Parameters["@itemid"].Value = nItemId;
                            mcmd.Parameters["@gid"].Value = gids[k];
                            mcmd.Parameters["@btntext"].Value = btnText[row][col];
                            mcmd.Parameters["@Itemfont"].Value = btnFont[row][col];
                            mcmd.ExecuteNonQuery();

                        }

                    }

                    k = 0;
                }

                mytran.Commit();

            }

            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);
            }

        }


        // the function that gets the first group based on the language
        public Int32 getFirstLanGroupID(Int32 lanid)
        {
            string sql = "select GroupID from groups where LanID=@lanid";


            try
            {


                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }



                Int32 gid;

                MySqlCommand mcom = new MySqlCommand();

                // began a tranjection in the database

                mcom.Connection = myconn.MysqlConnObject;
                mcom.CommandText = sql;
                mcom.Parameters.AddWithValue("@lanid", lanid);
                object result = mcom.ExecuteScalar();
                if (result != Convert.DBNull && result != null)
                    gid = Convert.ToInt32(result);
                else
                    gid = -1;

                return gid;

            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message + "Critical database operation error");

            }



        }



        // The function thzat gets the Child gid based on the parent gid given
        public DataTable getChildGids(int gid)
        {

            string sql = "select GroupID from groups where ParentGID= @pgid";
            DataTable dt = null;
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();

                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = sql;
                mcomm.Parameters.AddWithValue("@pgid", gid);
                MySqlDataAdapter myadpt = new MySqlDataAdapter(mcomm);
                myadpt.Fill(dt);


                return dt;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + "Error ffrom the database");

            }




        }

        /// <summary>
        /// Receves Langusge ID, returns groupID
        /// </summary>
        /// <param name="lanid"></param>
        /// <returns></returns>
        public List<Int32> getGoupsONLanIDroup(int lanid)
        {

            string sql = "select GroupID from groups where LanID=@lanid";
            DataTable dt = null;
            List<Int32> Groupid = new List<int>();
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();


                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = sql;
                mcomm.Parameters.AddWithValue("@lanid", lanid);

                MySqlDataAdapter myadpt = new MySqlDataAdapter(mcomm);
                myadpt.Fill(dt);
                foreach (DataRow row in dt.Rows)
                {
                    Groupid.Add(Convert.ToInt32(row["GroupID"]));
                }

                return Groupid;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + "Error ffrom the database");

            }
        }


        public Int32 getLanIDOnLanName(string lanname)
        {

            string sql = "select LanID from language where LanName=@name";

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }



                MySqlCommand mcomm = new MySqlCommand();


                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = sql;
                mcomm.Parameters.AddWithValue("@name", lanname);
                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull && result != null)
                {
                    return Convert.ToInt32(result);

                }
                else
                {
                    return -1;

                }


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + "Error ffrom the database");

            }



        }
        /// <summary>
        /// if first table is empty then return trye else return false
        /// </summary>
        /// <returns></returns>
        public bool isgroupTableEmpty()
        {

            string sql = "select count(*) from groups";

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();


                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.CommandText = sql;
                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull && result != null)
                {
                    result = Convert.ToInt32(result);

                }
                if ((Int32)result > 0)
                    return false;
                else
                    return true;


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + "Error ffrom the database");

            }


        }


        // the function that gets the itemgroup in a serial 

        public List<ExtItem> getRelatedItemBtns(Int32 itemid)
        {

            string sql = "select ItemGroup from groupitem where ItemID= @itemid";
            string sql2 = "select ItemID , ItemText, ItemFont from groupitem where ItemID=@itemid";
            string sql3 = "select ItemID, ItemText, ItemFont from groupitem where ItemGroup=@itgr";
            List<ExtItem> item = new List<ExtItem>();

            bool previous = false;
            Int32 cItemID = itemid;
            Int32 pItemID = itemid;

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }
                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@itemid", cItemID);
                object result = null;
                while (!previous)
                {
                    mcomm.Parameters["@itemid"].Value = cItemID;
                    result = mcomm.ExecuteScalar();
                    if (result != Convert.DBNull)
                    {
                        pItemID = cItemID;
                        cItemID = Convert.ToInt32(result);

                    }
                    else
                    {
                        previous = true;
                    }

                }

                // Now go forword to build the item class
                mcomm.CommandText = sql2;
                mcomm.Parameters["@itemid"].Value = cItemID;
                MySqlDataReader rd = mcomm.ExecuteReader();
                ExtItem it = new ExtItem();
                while (rd.Read())
                {
                    it.ItemID = Convert.ToInt32(rd["ItemID"]);
                    it.ItemText = Convert.ToString(rd["ItemText"]);
                    it.Font = Convert.ToString(rd["ItemFont"]);

                }
                rd.Close();
                item.Add(it);

                mcomm.CommandText = sql3;
                mcomm.Parameters.AddWithValue("@itgr", cItemID);

                // most ananoying 

                bool next = false;
                string itemtext;
                while (!next)
                {
                    mcomm.Parameters["@itgr"].Value = cItemID;
                    rd = mcomm.ExecuteReader();
                    if (rd.HasRows)
                    {
                        while (rd.Read())
                        {
                            cItemID = Convert.ToInt32(rd["ItemID"]);
                            itemtext = Convert.ToString(rd["ItemText"]);
                            string font = Convert.ToString(rd["ItemFont"]);
                            item.Add(new ExtItem(cItemID, itemtext, font));
                        }
                    }
                    else
                        next = true;

                    rd.Close();

                }

                return item;



            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + " A critical data base error please contract admin");

            }


        }


        /// <summary>
        /// the function that updates the displaygroup filed in the groupitem
        /// </summary>
        /// <param name="itemid"> the item id that needs to be updated</param>
        /// <param name="dgid"> the val which will be puted in the DisplayGroup field</param>
        public void updateDisplayGroup(Int32 itemid, Int32 dgid)
        {

            string sql = "update groupitem set DisplayGroup=@dgid where ItemID=@itemid";
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }
                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@dgid", dgid);
                mcomm.Parameters.AddWithValue("@itemid", itemid);
                mcomm.ExecuteNonQuery();


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + " Error from the database interface");


            }

        }


        public Int32 getDisplayGroup(Int32 itemid)
        {
            string sql = "select DisplayGroup from groupitem where ItemID=@itemid";
            try
            {
                if (myconn == null)
                    this.parpreConn();
                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }



                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@itemid", itemid);
                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull && result != null)
                {
                    return Convert.ToInt32(result);

                }
                else
                    return -1;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + "Error from the database");
            }


        }

        public void deleteAllRelatedItems(int itemid)
        {

            string sql = "select ItemID from groupitem where ItemGroup=@itemgr";
            string sql2 = "select DataID from groupitem where ItemID=itemid";
            string sql3 = "delete from textdata where DataID=@dataid";
            string sql4 = "delete from data where DataID=@dataid";
            string sql5 = "delete from groupitem where ItemID=@itemid";

            MySqlTransaction mytran = null;
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();
                mytran = this.myconn.MysqlConnObject.BeginTransaction();
                mcomm.Transaction = mytran;
                mcomm.Connection = this.myconn.MysqlConnObject;


                // Check and get all the items in the group

                List<ExtItem> items = this.getRelatedItemBtns(itemid);
                mcomm.CommandText = sql3;
                mcomm.Parameters.AddWithValue("dataid", 0);
                mcomm.CommandText = sql2;
                mcomm.Parameters.AddWithValue("@itemid", itemid);

                foreach (Item itv in items)
                {

                    mcomm.Parameters["@itemid"].Value = itv.ItemID;
                    object result = mcomm.ExecuteScalar();
                    if (result != null && result != Convert.DBNull)
                    {
                        result = Convert.ToInt32(result);
                        mcomm.CommandText = sql3;
                        mcomm.Parameters["@dataid"].Value = result;
                        mcomm.ExecuteNonQuery();
                        mcomm.CommandText = sql4;
                        mcomm.Parameters["@dataid"].Value = result;
                        mcomm.ExecuteNonQuery();

                    }


                }
                // Delete the items from the groupitems and all the data has been deleted

                mcomm.CommandText = sql5;
                mcomm.Prepare();
                items.Reverse();
                foreach (Item itv in items)
                {
                    mcomm.Parameters["@itemid"].Value = itv.ItemID;
                    mcomm.ExecuteNonQuery();

                }

                mytran.Commit();
            }
            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception("A Error has occured " + ex.Message);

            }


        }

        public void upDateItemText(int itemid, string itemtext)
        {

            string sql = "update groupitem set ItemText=@itemtext where ItemID=@itemid";
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.Connection = this.myconn.MysqlConnObject;
                mcomm.CommandText = sql;
                mcomm.Parameters.AddWithValue("@itemid", itemid);
                mcomm.Parameters.AddWithValue("@itemtext", itemtext);
                mcomm.ExecuteNonQuery();



            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);


            }
        }

        // Get the existing primary button group in the system
        public List<Int32> getPrimaryBtnGroup()
        {
            string sql = "select GroupID from groups where ParentGID is null";
            List<Int32> groupList = new List<int>();
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }



                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                DataTable dt = new DataTable();
                mcomm.Connection = this.myconn.MysqlConnObject;
                MySqlDataAdapter adpt = new MySqlDataAdapter(mcomm);
                adpt.Fill(dt);
                if (dt.Rows.Count > 0)
                    foreach (DataRow row in dt.Rows)
                        foreach (DataColumn col in dt.Columns)
                            if (col.ColumnName == "GroupID")
                                groupList.Add(Convert.ToInt32(row[col]));


                return groupList;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }


        }

        public void interChangeItems(int itemid, int itemid2)
        {

            string sql = "update groupitem set ItemID=@newitemid where ItemID=@itemid";
            // string sql2 ="select ItemGroup from groupitem where ItemID=@itemid";    
            string sql3 = "update groupitem set ItemGroup=@itgroup where ItemID=@itemid";
            //string sql4 = "select ItemID from groupitem where ItemGroup=@itemid";
            MySqlTransaction mytran = null;
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }



                MySqlCommand mcomm = new MySqlCommand();
                mytran = this.myconn.MysqlConnObject.BeginTransaction();
                mcomm.Transaction = mytran;
                mcomm.Connection = this.myconn.MysqlConnObject;
                //new coge after the failure
                List<ExtItem> firstrelatedListItem = this.getRelatedItemBtns(itemid);
                List<ExtItem> secRelatedListItem = this.getRelatedItemBtns(itemid2);
                // Make the itemGroup foreign key null in every related fileld
                mcomm.CommandText = sql3;
                mcomm.Parameters.AddWithValue("@itgroup", 0);
                mcomm.Parameters.AddWithValue("@itemid", 0);
                mcomm.Prepare();
                for (int i = 1; i < firstrelatedListItem.Count; i++)
                {
                    mcomm.Parameters["@itgroup"].Value = Convert.DBNull;
                    mcomm.Parameters["@itemid"].Value = firstrelatedListItem[i].ItemID;
                    mcomm.ExecuteNonQuery();

                }

                for (int i = 1; i < secRelatedListItem.Count; i++)
                {
                    mcomm.Parameters["@itgroup"].Value = Convert.DBNull;
                    mcomm.Parameters["@itemid"].Value = secRelatedListItem[i].ItemID;
                    mcomm.ExecuteNonQuery();
                }

                mcomm.CommandText = sql;
                mcomm.Parameters.AddWithValue("@newitemid", 0);
                mcomm.Prepare();
                for (int i = 0; i < firstrelatedListItem.Count; i++)
                {
                    // swiotch two items

                    mcomm.Parameters["@newitemid"].Value = -1;
                    mcomm.Parameters["@itemid"].Value = firstrelatedListItem[i].ItemID;
                    mcomm.ExecuteNonQuery();
                    //
                    mcomm.Parameters["@newitemid"].Value = firstrelatedListItem[i].ItemID;
                    mcomm.Parameters["@itemid"].Value = secRelatedListItem[i].ItemID;
                    mcomm.ExecuteNonQuery();
                    //
                    mcomm.Parameters["@newitemid"].Value = secRelatedListItem[i].ItemID;
                    mcomm.Parameters["@itemid"].Value = -1;
                    mcomm.ExecuteNonQuery();

                }

                // Now setup the link relation of the items
                mcomm.CommandText = sql3;
                mcomm.Prepare();
                for (int i = 1; i < firstrelatedListItem.Count; i++)
                {
                    mcomm.Parameters["@itgroup"].Value = firstrelatedListItem[i - 1].ItemID;
                    mcomm.Parameters["@itemid"].Value = firstrelatedListItem[i].ItemID;
                    mcomm.ExecuteNonQuery();

                }

                for (int i = 1; i < secRelatedListItem.Count; i++)
                {
                    mcomm.Parameters["@itgroup"].Value = secRelatedListItem[i - 1].ItemID;
                    mcomm.Parameters["@itemid"].Value = secRelatedListItem[i].ItemID;
                    mcomm.ExecuteNonQuery();
                }

                mytran.Commit();
                /*
                 * mcomm.CommandText = sql;
                 mcomm.Parameters.AddWithValue("@newitemid",-1);
                 mcomm.Parameters.AddWithValue("@itemid",itemid);
                 mcomm.ExecuteNonQuery();
                 mcomm.Parameters["@newitemid"].Value = -2;
                 mcomm.Parameters["@itemid"].Value = itemid2;
                 mcomm.ExecuteNonQuery();
               
                 * 
                 * */

            }


            catch (Exception ex)
            {

                mytran.Rollback();
                throw new Exception(ex.Message);

            }



        }

        public Byte getDataOpt(Int32 dataid)
        {

            string sql = "select DisOpt from data where DataID=@dataid";

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }




                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@dataid", dataid);

                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull)
                {
                    return Convert.ToByte(result);

                }
                else
                {

                    return 0;
                }




            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }


        }

        public void setDataOpt(Int32 dataid, Byte data)
        {

            string sql = "update data set DisOpt=@data where DataID=@dataid";

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@dataid", dataid);
                mcomm.Parameters.AddWithValue("@data", data);
                mcomm.ExecuteNonQuery();


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }

        }

        public void updateHeading(Int32 dataid, string str)
        {

            string sql = "update data set DataHeading=@dh where DataID=@dataid";

            try
            {


                if (myconn == null)
                    this.parpreConn();
                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@dh", str);
                mcomm.Parameters.AddWithValue("@dataid", dataid);
                mcomm.ExecuteNonQuery();



            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }

        }

        public string getDataHeading(Int32 dataid)
        {

            string sql = "select DataHeading from data where DataID=@dataid";

            try
            {


                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;

                mcomm.Parameters.AddWithValue("@dataid", dataid);
                object result = mcomm.ExecuteScalar();
                return Convert.ToString(result);


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }

        }


        // function that displays the msg in the window

        public List<string> getGobalMassage()
        {

            string sql = "select Message from macsettings ";
            List<string> str = new List<string>();
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                DataTable dt = new DataTable();
                MySqlDataAdapter ad = new MySqlDataAdapter(mcomm);
                ad.Fill(dt);
                foreach (DataRow r in dt.Rows)
                {
                    str.Add((string)r[0]);

                }
                return str;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }



        }


        public List<Item> getGMSG()
        {


            string sql = "select * from macsettings ";
            List<Item> items = new List<Item>();
            try
            {
                if (myconn == null)
                    this.parpreConn();
                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }




                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                DataTable dt = new DataTable();
                MySqlDataAdapter ad = new MySqlDataAdapter(mcomm);
                ad.Fill(dt);
                foreach (DataRow r in dt.Rows)
                {
                    items.Add(new Item());

                    items[items.Count - 1].ItemID = (Int32)r["MessageID"];
                    items[items.Count - 1].ItemText = (String)r["Message"];

                }

                return items;


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }



        }


        public void delGBMessage(Int32 msgID)
        {


            string sql = "delete from macsettings where MessageID= @msgid";

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }




                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@msgid", msgID);
                mcomm.ExecuteNonQuery();


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }


        }

        public void insertGBMessage(String message)
        {
            string sql = "select MAX(MessageID) from macsettings";
            String sql2 = "insert into macsettings(MessageID,Message) values(@msgid,@msg)";
            Int32 maxID;
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }



                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull)
                {
                    maxID = Convert.ToInt32(result) + 1;

                }
                else
                    maxID = 1;

                mcomm.CommandText = sql2;
                mcomm.Parameters.AddWithValue("@msgid", maxID);
                mcomm.Parameters.AddWithValue("@msg", message);
                mcomm.ExecuteNonQuery();



            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }


        }

        public void updateTxtData(int dataid, string filePath)
        {



            MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
            string SQL;
            long FileSize;
            byte[] rawData;
            FileStream fs;


            try
            {

                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }



                fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                FileSize = fs.Length;

                rawData = new byte[FileSize];
                fs.Read(rawData, 0, (Int32)FileSize);
                fs.Close();



                SQL = "update textdata set Text=@text, Size=@size where DataID=@dataid";

                cmd.Connection = this.myconn.MysqlConnObject;
                cmd.CommandText = SQL;

                cmd.Parameters.AddWithValue("@size", FileSize);
                cmd.Parameters.AddWithValue("@text", rawData);
                cmd.Parameters.AddWithValue("@dataid", dataid);

                cmd.ExecuteNonQuery();



            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                throw new Exception(ex.Message);

            }



        }


        public Int32 getItemId(string labelText)
        {

            string sql = "select ItemID from groupitem where ItemText=@text";
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@text", labelText);
                object result = mcomm.ExecuteScalar();
                if (result != Convert.DBNull)
                {
                    return Convert.ToInt32(result);

                }
                else
                    return -1;




            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }




        }



        public void upDateItemFont(Int32 itemID, string font)
        {

            string sql = "update groupitem set ItemFont=@font where ItemID=@id";

            try
            {

                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                mcomm.Parameters.AddWithValue("@font", font);
                mcomm.Parameters.AddWithValue("@id", itemID);
                mcomm.ExecuteNonQuery();



            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }


        }



        // custom class for the database handling 

        public void createitemInGroup(List<Int32> gids, List<List<ExtItem>> labelItems)
        {


            // Now update the each itemgroup fields its the new one that i have to fix up.


            string sql = "insert into groupitem(ItemID , DataID, GroupID, ItemText , ItemGroup, ItemFont) values( @itemID, NULL , @gID , @btntext, @itemgr, @Itemfont)";
            string sql2 = "select MAX(ItemID) from groupitem";


            MySqlCommand mcmd;
            MySqlTransaction mytran = null;

            try
            {

                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }


                mcmd = new MySqlCommand();
                mytran = myconn.MysqlConnObject.BeginTransaction();
                mcmd.Connection = myconn.MysqlConnObject;
                mcmd.CommandText = sql2;
                mcmd.Transaction = mytran;
                object result = mcmd.ExecuteScalar();
                Int32 nItemId;

                if (result == Convert.DBNull)
                    nItemId = 0;
                else
                    nItemId = Convert.ToInt32(result);

                // now inserting dataitems
                mcmd.CommandText = sql;


                mcmd.Parameters.AddWithValue("@itemID", 1);
                mcmd.Parameters.AddWithValue("@gid", 1);
                mcmd.Parameters.AddWithValue("@btntext", "BTNTEXT");
                mcmd.Parameters.AddWithValue("@itemgr", Convert.DBNull);
                mcmd.Parameters.AddWithValue("@Itemfont", "null");
                mcmd.Prepare();
                int k = 0;
                int row = 0;
                int col = 0;
                int rownum = labelItems.Count;
                int colnum = labelItems[0].Count;


                for (; col < colnum; col++)
                {
                    for (row = 0; row < rownum; row++)
                    {
                        if (row == 0)
                        {
                            nItemId++;
                            mcmd.Parameters["@itemgr"].Value = Convert.DBNull;
                            mcmd.Parameters["@itemid"].Value = nItemId;
                            mcmd.Parameters["@gid"].Value = gids[k];
                            mcmd.Parameters["@btntext"].Value = labelItems[row][col].ItemText;
                            mcmd.Parameters["@Itemfont"].Value = labelItems[row][col].Font;
                            Console.WriteLine(labelItems[row][col]);
                            mcmd.ExecuteNonQuery();

                        }
                        else
                        {
                            mcmd.Parameters["@itemgr"].Value = nItemId;
                            nItemId++;
                            k++;
                            mcmd.Parameters["@itemid"].Value = nItemId;
                            mcmd.Parameters["@gid"].Value = gids[k];
                            mcmd.Parameters["@btntext"].Value = labelItems[row][col].ItemText;
                            mcmd.Parameters["@Itemfont"].Value = labelItems[row][col].Font;
                            mcmd.ExecuteNonQuery();

                        }

                    }

                    k = 0;
                }

                mytran.Commit();

            }

            catch (Exception ex)
            {
                mytran.Rollback();
                throw new Exception(ex.Message);
            }

        }

        // functiion that sets the dataId to null
        public void setDataIDTONull(Int32 itemID)
        {
            string sql = "update groupitem set DataID=null where ItemID=@it";

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;

                mcomm.Parameters.AddWithValue("@it", itemID);
                mcomm.ExecuteNonQuery();



            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "can not set null");

            }



        }


        public void delGroup(Int32 gid)
        {

            string sql = "delete from groups where GroupID=@gid";
            try
            {
                if (myconn == null)
                    this.parpreConn();
                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;

                mcomm.Parameters.AddWithValue("@gid", gid);
                mcomm.ExecuteNonQuery();



            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }



        }


        public void setDisplayGroupTONull(Int32 itemID)
        {
            string sql = "update groupitem set DisplayGroup=null where ItemID=@it";

            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;

                mcomm.Parameters.AddWithValue("@it", itemID);
                mcomm.ExecuteNonQuery();



            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "can not set null");

            }



        }


        /// <summary>
        /// the function that deletes items recursivesly
        /// </summary>

        public void delItems(int itemID)
        {

            List<ExtItem> it = this.getRelatedItemBtns(itemID);

            int dg = this.getDisplayGroup(itemID);
            if (dg == -1)
            {

                Int32 dgroup = this.getGroupIDOnItemID(itemID);

                this.deleteAllRelatedItems(itemID);


                DataTable dt = this.getAllGroupItems(dgroup);

                if (dt.Rows.Count < 1)
                    this.delGroup(dgroup);


            }
            else
            {
                // delete group on the 

                this.dGroup(dg);

                this.delItems2(itemID);


            }



        }




        public void dGroup(Int32 gid)
        {

            DataTable dt = this.getAllGroupItems(gid);
            int dg;
            foreach (DataRow r in dt.Rows)
            {

                dg = this.getDisplayGroup((Int32)r["ItemID"]);
                if (dg == -1)
                {

                    this.delItems2((Int32)r["ItemID"]);

                }
                else
                {
                    this.dGroup(dg);

                }




            }

            // delete the group
            dt.Reset();
            dt = this.getAllGroupItems(gid);

            if (dt.Rows.Count < 1)
                this.delGroup(gid);



        }

        public void delItems2(int itemID)
        {

            //List<ExtItem> it = this.getRelatedItemBtns(itemID);

            this.deleteAllRelatedItems(itemID);


        }

        /// <summary>
        /// gets all the available lanhuages in form of a datatable 
        /// </summary>
        /// <returns></returns>
        public DataTable getAllLanData()
        {

            string sql = "select * from language";
            DataTable dt = new DataTable();
            try
            {
                if (myconn == null)
                    this.parpreConn();

                if (!myconn.isConnected())
                {
                    this.myconn.reConnect();

                }

                MySqlCommand mcomm = new MySqlCommand();
                mcomm.CommandText = sql;
                mcomm.Connection = this.myconn.MysqlConnObject;
                MySqlDataAdapter adapter = new MySqlDataAdapter(mcomm);
                adapter.Fill(dt);
                return dt;


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }

        }

        // Check if the databse Config is correct
        public MySQLMessage checkConfig(String svrAdd, String svrUser, String databse, int svrPort, String pass)
        {

            try
            {
                MySqlConnection conn;
                conn = new MySqlConnection("server=" + svrAdd + ";user=" + svrUser + ";database=" + databse + ";port=" + svrPort + ";password=" + pass);
                conn.Open();
                return MySQLMessage.Success;
            }

            catch (MySqlException ex)
            {

                switch (ex.Number)
                {

                    case 1042:
                    case 0:
                        return MySQLMessage.HostError;


                    case 1045:
                        return MySQLMessage.AuthencationError;


                    case 1049:
                        return MySQLMessage.UnknownDataBase;



                    default:
                        return MySQLMessage.UnKnown;



                }

            }

        }


        // Create a new user in the dataBase
        public void createSchemaForNUser(String svradd , int port , String runame , String rpass , String newUsername , String nDataBase , String nUsrPass)
        {


            try
            {

                string connStr = "server=" + svradd + ";user=" + runame + ";port=" + port + ";password=" + rpass + ";";

                MySqlConnection conn = new MySqlConnection(connStr);
                conn.Open();
                // CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
                string sql = "Create user '"+ newUsername +"'@'"+svradd +"' identified by " +"'"+ nUsrPass+ "'";
                Console.WriteLine(sql);
                MySqlCommand comm = new MySqlCommand(sql, conn);
                comm.ExecuteNonQuery();
                comm.CommandText =  "drop database if Exists " + nDataBase;
                comm.ExecuteNonQuery();

                comm.CommandText = "Create database " + nDataBase;

                Console.WriteLine(comm.CommandText);
                
                comm.ExecuteNonQuery();
                comm.CommandText = "Use "+nDataBase;
                comm.ExecuteNonQuery();
                comm.CommandText = "GRANT ALL PRIVILEGES ON " + nDataBase + " TO '" + newUsername + "'@'" + svradd + "' WITH GRANT OPTION ;";
                comm.ExecuteNonQuery();
            }
            catch(MySqlException ex)
            {
                
                throw new Exception(ex.Message); 

            }

        
        
        
        }

        public void createDataBase(String svradd, int port, String uname, String pass, String nDataBase)
        {

           
            string connStr = "server=" + svradd + ";user=" + uname + ";port=" + port + ";password=" + pass + ";";
            try
            {
                MySqlConnection conn = new MySqlConnection(connStr);
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Create database " + nDataBase;



                comm.ExecuteNonQuery();


            }
            catch(Exception ex)
            {

                throw new Exception(ex.Message);


            }
        
        
        
        }



        // function that displays the msg in the window

        //public List<Item> getGobalMassageItem()
        //{

        //    string sql = "select * from macsettings";
        //    List<Item> items = new List<Item>();
        //    try
        //    {
        //        if (myconn == null)
        //            this.parpreConn();

        //        if (!myconn.isConnected())
        //        {
        //            this.myconn.reConnect();

        //        }

        //        MySqlCommand mcomm = new MySqlCommand();
        //        mcomm.CommandText = sql;
        //        mcomm.Connection = this.myconn.MysqlConnObject;
        //        DataTable dt = new DataTable();
        //        MySqlDataAdapter ad = new MySqlDataAdapter(mcomm);
        //        ad.Fill(dt);
        //        foreach (DataRow r in dt.Rows)
        //        {
        //            items.Add(new Item());
        //            foreach (DataColumn col in dt.Columns)
        //            {
        //                if (col.ColumnName == "MessageID")
        //                {
        //                    items[items.Count - 1].ItemID = Convert.ToInt32(r[col]);
        //                }
        //                else
        //                {
        //                    items[items.Count - 1].ItemText = Convert.ToString(r[col]);
        //                }

                    
                    
        //            }

        //        }
        //        return items;

        //    }
        //    catch (Exception ex)
        //    {

        //        throw new Exception(ex.Message);

        //    }



        //}





    } // end of the class



} // end of the namespace


